I am trying to run the following Linq query from MySQL client
query = query.Where(c => c.CustomerRoles
.Select(cr => cr.Id)
.Intersect(customerRoleIds)
.Any()
);
This code looks okay, but gives the error:
System.NotSupportedException: Specified method is not supported.at MySql.Data.Entity.SqlGenerator.Visit(DbIntersectExpression expression)
This looks to me like an issue with .Intersect. Can anybody tell me the cause of this error and how to fix it?
Amir Sherafatian :
i think @GertArnold's post is a correct and best of the answers, but i'm wonder why have you gotten NotSupportedException yet ? so the problem should not be from intersect probably.\n\nwhere is customerRoleIds come from ? is it IQueryable<T> ?\n\nbreak the query, and complete it step by step. \n\nif you don't get exception at this lines:\n\nvar a = query.Select(c => new { \n c, \n CustomerRoleIDList = c.CustomerRoles.Select(cr => cr.Id).AsEnumerable()\n })\n .ToList();\n\nvar b = customerRoleIds.ToList();\n\n\nyou must get the result by this:\n\nvar b = query.Where(c => c.CustomerRoles.any(u => customerRoleIds.Contains(u.Id)))\n .ToList();\n\n\nif you get exception by above query, you can try this final solution to fetch data first, but note by this, all data will be fetched in memory first:\n\nvar a = query.Select(c => new { \n c, \n CustomerRoleIDList = c.CustomerRoles.Select(cr => cr.Id).AsEnumerable() \n })\n .ToList();\n\nvar b = a.Where(c => c.CustomerRoleIDList.any(u => customerRoleIds.Contains(u)))\n .Select(u => u.c)\n .ToList();\n",
2014-05-04T17:30:33
Gert Arnold :
Using Intersect or Except is probably always troublesome with LINQ to a SQL backend. With Sql Server they may produce horrible SQL queries.\n\nUsually there is support for Contains because that easily translates to a SQL IN statement. Your query can be rewritten as\n\nquery = query.Where(c => c.CustomerRoles\n .Any(cr => customerRoleIds.Contains(cr.Id)));\n\n\nI don't think that customerRoleIds will contain many items (typically there won't be hundreds of roles), otherwise you should take care not to hit the maximum number of items allowed in an IN statement.",
2014-04-29T13:22:16
Honorable Chow :
query.Where(c => c.CustomerRoles\n .Any(v=>customerRoleIds.Any(e=>e == v.Id))\n .Select(cr => cr.Id))\n .ToList();\n",
2014-04-28T15:39:58